package com.fau.sd.sync;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.sd.util.PhoneNumUtil;
import com.fau.sd.util.SerialNumUtil;
import com.fau.sd.util.SystemConfig;
import com.fau.sd.util.UuidUtil;

@Service
@Transactional(readOnly = true)
public class SyncData {
	
	@Resource
	private JdbcTemplate jdbcSrc;
	@Resource
	private JdbcTemplate jdbcTarget;
	
	@Scheduled(cron = "*/15 * * * * ?")
	public void run() {
		syncDataService();
		System.out.println("Running.....");
	}
	
	/**
	 * @desc 数据扫描业务方法
	 * @author kangyj
	 * @datetime 2015年1月4日,下午8:43:56
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	private void syncDataService() {
		List<Map<String, Object>> cdrList = this.getCdrData();
		for(Map<String, Object> map : cdrList) {
			// 判断呼入呼出
			if(SystemConfig.OUT_TO_IN_TYPE.equals(map.get("call_type"))) {					// 呼入
				
				String eid = findIncomingEid(map.get("did").toString());
				List<Map<String, Object>> callerList = validCallerExist(map, eid);
				if(callerList.size() == 0) {												// 无来电信息的才保存任务。
					List<Object> callTask = this.findCall(map);
					if(callTask.size() == 0) {												// 判断任务是否存在
						this.saveCall(map);													// 保存任务
					} else {
						this.updateCallSql(callTask, map);									// 更新任务
					}
				}
				
				if(callerList.size() > 0) {													// 有来电信息先删除，再保存
					this.delCaller(callerList.get(0), eid);
				}
				this.saveCallCaller(map);													// 保存来电信息表
				
			} else if(SystemConfig.IN_TO_OUT_TYPE.equals(map.get("call_type"))) {			// 呼出外线(接通的才计算话费)
				this.savemcallExhale(map);													// 保存呼出信息
				if(SystemConfig.CTI_ANSWERED_TYPE.equals(map.get("disposition"))) {		
					this.saveCallRecord(map);												// 保存话单
				}
			}
			if(SystemConfig.CTI_ANSWERED_TYPE.equals(map.get("disposition"))) {
				this.saveRecording(map);													// 保存录音记录
			}
		}
	}
	
	/**
	 * @desc 获取cdr数据
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月4日,下午7:48:00
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Map<String, Object>> getCdrData() {
		StringBuilder cdrSql = new StringBuilder("select a.id, a.src, a.dst, a.billsec, a.disposition, a.trunk, a.calldate, a.answer_time, a.hangup_time, a.recordingfile, a.did, a.call_type, a.uuid, a.is_deal from cdr a where a.is_deal=?");
		StringBuilder cdrSql1 = new StringBuilder("select a.id, a.src, a.dst, a.billsec, a.disposition, a.trunk, a.calldate, a.answer_time, a.hangup_time, a.recordingfile, a.did, a.call_type, a.uuid, a.is_deal from cdr a where a.is_deal=? and a.uuid=? and a.call_order=? ");
		
		List<Map<String, Object>> cdrList = jdbcSrc.queryForList(cdrSql.toString(), new Object[]{SystemConfig.IS_NOT_DEAL_TYPE});
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		
		for(Map map : cdrList) {		// 遍历已保存任务未处理来电信息的和彻底未处理的cdr数据
			if(SystemConfig.CTI_ANSWERED_TYPE.equals(map.get("disposition"))) {
				list.add(map);				// 接了
			} else {						// 没接找order=1的
				List<Map<String, Object>> cdrList1 = jdbcSrc.queryForList(cdrSql1.toString(), new Object[]{
					SystemConfig.IS_NOT_DEAL_TYPE, map.get("uuid").toString(), SystemConfig.CALL_ORDER_FIRST});
				if(cdrList1.size() > 0) {
					list.add(cdrList1.get(0));
				}
			}
		}
		this.updateCdrStatus(cdrList);
		return list;
	}
	
	/**
	 * @desc 查询任务是否存在
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月8日,下午2:55:07
	 */
	public List<Object> findCall(Map<String, Object> map) {
		
		// 查询外线号码和所绑定的企业
		StringBuilder getTelSql = new StringBuilder("select a.call_id from medical_call_tel_info a where a.phone_num=? ");
		List<Map<String, Object>> tel = jdbcTarget.queryForList(getTelSql.toString(), new Object[]{map.get("src")});
		List<Object> callTask = new ArrayList<Object>();
		List<Map<String, Object>> call = new ArrayList<Map<String,Object>>();
		if(tel.size() > 0) {
			// 任务各表进行检索，查询任务。
			StringBuilder getCallSql = new StringBuilder("select a.id, a.category, a.incoming_count, a.create_time from medical_call a where a.id=? ");
			call = jdbcTarget.queryForList(getCallSql.toString(), new Object[]{tel.get(0).get("call_id")});
			if(call.size() > 0) {
				callTask.add(SystemConfig.CALL_TASK_UNALLOCATED);
			} else {
				StringBuilder getCallUntSql = new StringBuilder("select a.id, a.category, a.incoming_count, a.create_time from medical_call_untreated a where a.id=? ");
				call = jdbcTarget.queryForList(getCallUntSql.toString(), new Object[]{tel.get(0).get("call_id")});
				if(call.size() > 0) {
					callTask.add(SystemConfig.CALL_TASK_UNTREATED);
				} else {
					StringBuilder getCallPenSql = new StringBuilder("select a.id, a.category, a.incoming_count, a.create_time from medical_call_pending a where a.id=? ");
					call = jdbcTarget.queryForList(getCallPenSql.toString(), new Object[]{tel.get(0).get("call_id")});
					if(call.size() > 0) {
						callTask.add(SystemConfig.CALL_TASK_PENDING);
					} else {
						StringBuilder getCallEndSql = new StringBuilder("select a.id, a.category, a.incoming_count, a.create_time from medical_call_end a where a.id=? ");
						call = jdbcTarget.queryForList(getCallEndSql.toString(), new Object[]{tel.get(0).get("call_id")});
						if(call.size() > 0) {
							callTask.add(SystemConfig.CALL_TASK_END);
						} else {
							StringBuilder getCallTerSql = new StringBuilder("select a.id, a.category, a.incoming_count, a.create_time from medical_call_terminate a where a.id=? ");
							call = jdbcTarget.queryForList(getCallTerSql.toString(), new Object[]{tel.get(0).get("call_id")});
							if(call.size() > 0) {
								callTask.add(SystemConfig.CALL_TASK_TERMINATE);
							}
						}
					}
				}
			}
			if(callTask.size() > 0) {
				callTask.add(call.get(0).get("id"));
				callTask.add(call.get(0).get("category"));
				callTask.add(call.get(0).get("incoming_count"));
				callTask.add(call.get(0).get("create_time"));
			}
		}
		return callTask;
	}
	
	/**
	 * @desc 保存一次进线任务，保存客户号码
	 * @param map
	 * @author kangyj
	 * @datetime 2015年1月8日,下午3:27:23
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveCall(Map<String, Object> map) {
		StringBuilder callSql = new StringBuilder("insert into medical_call(id, serial_num, category, outside_num, related_phone, update_time, create_time, hospital_id, grade, incoming_count) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
		// 任务id
		String callId = UuidUtil.getUuid();
		// 任务类别
		String category = "";
		if(SystemConfig.CTI_ANSWERED_TYPE.equals(map.get("disposition").toString())) {
			category = SystemConfig.CALL_FIRST_TYPE;
		} else {
			category = SystemConfig.CALL_OMISSION_TYPE;
		}
		// 企业id
		String eid = this.findIncomingEid(map.get("did").toString());
		jdbcTarget.update(callSql.toString(), new Object[]{callId, SerialNumUtil.code(), category, map.get("did"), map.get("src"), new Date(), new Date(), eid, SystemConfig.CALL_TASK_GRADE_INIT, 1});
		
		// 保存电话到电话信息表
		StringBuilder telSql = new StringBuilder("insert into medical_call_tel_info(id, phone_num, type, is_main, call_id, create_time, update_time, eid) values(?, ?, ?, ?, ?, ?, ?, ?) ");
		String validNum = PhoneNumUtil.checkNumber(map.get("src").toString()).toString();
		String type = "";
		if(SystemConfig.CELLPHONE_TYPE.equals(validNum.substring(0, validNum.lastIndexOf(",")))) {
			type = SystemConfig.CUSTOMER_TEL_PHONE_TYPE;
		} else if(SystemConfig.FIXEDPHONE_TYPE.equals(validNum.substring(0, validNum.lastIndexOf(",")))) {
			type = SystemConfig.CUSTOMER_TEL_FIXED_TYPE;
		}
		jdbcTarget.update(telSql.toString(), new Object[]{UuidUtil.getUuid(), map.get("src"), type, SystemConfig.CUSTOMER_TEL_IS_MAIN, callId, new Date(), new Date(), eid});
	}
	
	/**
	 * @desc 判断任务所处状态，更新类别、呼入次数
	 * @author kangyj
	 * @datetime 2015年1月8日,下午3:34:49
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateCallSql(List<Object> callTask, Map<String, Object> map) {
		StringBuilder updateTypeSql = new StringBuilder("");
		StringBuilder updateCountSql = new StringBuilder("");
		if(SystemConfig.CALL_TASK_UNALLOCATED.equals(callTask.get(0))) {		// 未分配任务
			updateTypeSql.append("update medical_call a set a.category=?, a.incoming_count=?, a.update_time=? where a.id=? ");
			updateCountSql.append("update medical_call a set a.incoming_count=?, a.update_time=? where a.id=? ");
		} else if(SystemConfig.CALL_TASK_UNTREATED.equals(callTask.get(0))) {	// 未处理
			updateTypeSql.append("update medical_call_untreated a set a.category=?, a.incoming_count=?, a.update_time=? where a.id=? ");
			updateCountSql.append("update medical_call_untreated a set a.incoming_count=?, a.update_time=? where a.id=? ");
		} else if(SystemConfig.CALL_TASK_PENDING.equals(callTask.get(0))) {		// 待处理
			updateTypeSql.append("update medical_call_pending a set a.category=?, a.incoming_count=?, a.update_time=? where a.id=? ");
			updateCountSql.append("update medical_call_pending a set a.incoming_count=?, a.update_time=? where a.id=? ");
		}
		this.updateCall(updateTypeSql, updateCountSql, callTask, map);
	}
	
	/**
	 * @desc 更新任务类别、次数
	 * @author kangyj
	 * @datetime 2015年1月8日,下午10:10:19
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateCall(StringBuilder updateTypeSql, StringBuilder updateCountSql, List<Object> callTask, Map<String, Object> map) {
		
		if(SystemConfig.CALL_TASK_UNALLOCATED.equals(callTask.get(0)) || SystemConfig.CALL_TASK_UNTREATED.equals(callTask.get(0)) || SystemConfig.CALL_TASK_PENDING.equals(callTask.get(0))) {
			int incoming_count = Integer.parseInt(callTask.get(3).toString()) + 1;
			
			if(SystemConfig.CALL_FIRST_TYPE.equals(callTask.get(2)) || SystemConfig.CALL_SECOND_TYPE.equals(callTask.get(2))) {						// 待回访更新类别次数、二次进线更新次数
				jdbcTarget.update(updateTypeSql.toString(), new Object[]{SystemConfig.CALL_SECOND_TYPE, incoming_count, new Date(), callTask.get(1)});
			} else if(SystemConfig.CALL_OMISSION_TYPE.equals(callTask.get(2))) {																	// 漏线
				Date DrainLineTime = DateUtils.addMilliseconds((Date) callTask.get(4), SystemConfig.EDIT_DRAIN_LINE_TIME);
				
				if(((Date) map.get("calldate")).after(DrainLineTime) && SystemConfig.CTI_ANSWERED_TYPE.equals(map.get("disposition"))) {			// 15分钟以后呼入接起
					jdbcTarget.update(updateTypeSql.toString(), new Object[]{SystemConfig.CALL_SECOND_TYPE, incoming_count, new Date(), callTask.get(1)});
				} else if(((Date) map.get("calldate")).before(DrainLineTime) && SystemConfig.CTI_ANSWERED_TYPE.equals(map.get("disposition"))) {	// 15分钟以内接起
					jdbcTarget.update(updateTypeSql.toString(), new Object[]{SystemConfig.CALL_FIRST_TYPE, incoming_count, new Date(), callTask.get(1)});
				} else {
					jdbcTarget.update(updateCountSql.toString(), new Object[]{incoming_count, new Date(), callTask.get(1)});
				}
				
			} else {																	// 废线
				jdbcTarget.update(updateCountSql.toString(), new Object[]{incoming_count, new Date(), callTask.get(1)});
			}
		} else if(SystemConfig.CALL_TASK_END.equals(callTask.get(0))) {					// 已结束任务，生成到未处理
			
			StringBuilder getEndSql = new StringBuilder("select * from medical_call_end a where a.id=? ");
			Map<String, Object> callEnd = jdbcTarget.queryForMap(getEndSql.toString(), new Object[]{callTask.get(1)});
			StringBuilder saveCallUntSql = new StringBuilder("insert into medical_call_untreated(id, serial_num, category, remark, create_user, outside_num, related_phone, allocation_time, update_time, create_time, hospital_id, center_id, group_id, handle_user_id, grade, incoming_count, transfer_user) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
			
			// 判断类别
			String category = callEnd.get("category").toString();
			if(SystemConfig.CALL_FIRST_TYPE.equals(category)) {
				category = SystemConfig.CALL_SECOND_TYPE;
			}
			
			jdbcTarget.update(saveCallUntSql.toString(), new Object[]{callEnd.get("id"), callEnd.get("serial_num"), category, callEnd.get("remark"), callEnd.get("create_user"), 
				callEnd.get("outside_num"), callEnd.get("related_phone"), callEnd.get("allocation_time"), new Date(), callEnd.get("create_time"), callEnd.get("hospital_id"), callEnd.get("center_id"), callEnd.get("group_id"), callEnd.get("handle_user_id"), callEnd.get("grade"), callEnd.get("incoming_count"), SystemConfig.CALL_UNT_NO_TRANSFER});
		
		} else if(SystemConfig.CALL_TASK_TERMINATE.equals(callTask.get(0))) {			// 已终止任务，生成到未分配
			StringBuilder getTerSql = new StringBuilder("select * from medical_call_terminate a where a.id=? ");
			Map<String, Object> callTer = jdbcTarget.queryForMap(getTerSql.toString(), new Object[]{callTask.get(1)});
			StringBuilder saveCallSql = new StringBuilder("insert into medical_call(id, serial_num, category, remark, create_user, outside_num, related_phone, allocation_time, update_time, create_time, hospital_id, center_id, group_id, handle_user_id, grade, incoming_count) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
			
			// 判断类别
			String category = callTer.get("category").toString();
			if(SystemConfig.CALL_FIRST_TYPE.equals(category)) {
				category = SystemConfig.CALL_SECOND_TYPE;
			}
			
			jdbcTarget.update(saveCallSql.toString(), new Object[]{callTer.get("id"), callTer.get("serial_num"), category, callTer.get("remark"), callTer.get("create_user"), 
				callTer.get("outside_num"), callTer.get("related_phone"), callTer.get("allocation_time"), new Date(), callTer.get("create_time"), callTer.get("hospital_id"), callTer.get("center_id"), callTer.get("group_id"), callTer.get("handle_user_id"), callTer.get("grade"), 0});
		}
	}
	
	/**
	 * @desc 查询号码是否已经生成来电信息
	 * @param map
	 * @param eid
	 * @return
	 * @author kangyj
	 * @datetime 2015年3月12日,下午6:03:36
	 */
	public List<Map<String, Object>> validCallerExist(Map<String, Object> map, String eid) {
		StringBuilder validCallerSql = new StringBuilder("select * from medical_call_caller a where 1=1 and a.caller_num=? and a.hospital_id=? and a.serial_num is null ");
		List<Map<String, Object>> list = jdbcTarget.queryForList(validCallerSql.toString(), new Object[]{map.get("src").toString(), eid});
		return list;
	}
	
	/**
	 * @desc 删除保存任务生成的来电信息
	 * @param map
	 * @param eid
	 * @author kangyj
	 * @datetime 2015年3月12日,下午6:03:17
	 */
	public void delCaller(Map<String, Object> map, String eid) {
		StringBuilder delCallerSql = new StringBuilder("delete from medical_call_caller where caller_num=? and hospital_id=? and serial_num is null ");
		jdbcTarget.update(delCallerSql.toString(), new Object[]{map.get("caller_num").toString(), eid});
	}
	
	/**
	 * @desc 数据插入到来电信息表中
	 * @param map
	 * @author kangyj
	 * @datetime 2015年1月4日,下午10:35:07
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveCallCaller(Map<String, Object> map) {
		StringBuilder callerSql = new StringBuilder("insert into medical_call_caller values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		// 是否接听
		String whetherAnswer = "";
		if(SystemConfig.CTI_ANSWERED_TYPE.equals(map.get("disposition"))) {
			whetherAnswer = SystemConfig.MEDICAL_ANSWERED_TYPE;
		} else {
			whetherAnswer = SystemConfig.MEDICAL_NO_ANSWER_TYPE;
		}
		// 接听坐席id
		String answerSeat = "";
		if(!map.get("dst").toString().equals(map.get("did").toString())) {		// 如果被叫dst和外线号码相同，说明无人接听
			answerSeat = this.findAnswerSeat(map.get("dst").toString());
		}
		
		// 企业id
		String eid = this.findIncomingEid(map.get("did").toString());
		// 来电号码归属地
		String address = this.findSrcAddress(map.get("src").toString());
		String province = "";
		String city = "";
		if(StringUtils.isNotBlank(address) && !SystemConfig.INVALIDPHONE.equals(address)) {
			province = address.substring(0, address.lastIndexOf("|"));
			city = address.substring(address.lastIndexOf("|") + 1);
		} else if(StringUtils.isBlank(address)) {		// 判断状态，过滤不规则号码3188819922
			province = "本地";
			city = "本地";
		}
		jdbcTarget.update(callerSql.toString(), new Object[]{UuidUtil.getUuid(), SerialNumUtil.code(), map.get("did"), map.get("src"), map.get("calldate"), map.get("answer_time"), map.get("dst"), whetherAnswer, map.get("billsec"), answerSeat, 0, province, city, eid});
	}
	
	/**
	 * @desc 保存呼出信息
	 * @param map 一条cdr数据记录
	 * @author kangyj
	 * @datetime 2015年1月6日,下午1:43:29
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void savemcallExhale(Map<String, Object> map) {
		StringBuilder exhaleSql = new StringBuilder("insert into medical_call_exhale values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		// 操作人
		String operator = this.findAnswerSeat(map.get("src").toString());
		// 组
		String groupId = this.findSeatGroup(operator);
		// 中心
		String centerId = this.findSeatCenter(groupId);
		// 企业id
		String eid = this.findOutgoingEid(operator);
		String address = "";
		if(StringUtils.isNotBlank(map.get("dst").toString())) {
			address = this.findDstAddress(map.get("dst").toString());
		}
		String province = "";
		String city = "";
		if(StringUtils.isNotBlank(address) && !SystemConfig.INVALIDPHONE.equals(address)) {
			province = address.substring(0, address.lastIndexOf("|"));
			city = address.substring(address.lastIndexOf("|") + 1);
		} else if(StringUtils.isBlank(address)) {								// 判断状态，过滤不规则号码3188819922
			province = "本地";
			city = "本地";
		}
		jdbcTarget.update(exhaleSql.toString(), new Object[]{UuidUtil.getUuid(), map.get("src"), map.get("dst"), map.get("calldate"), map.get("hangup_time"), map.get("billsec"), operator, groupId, centerId, province, city, eid});
	}
	
	/**
	 * @desc 保存话单记录
	 * @param map
	 * @author kangyj
	 * @datetime 2015年1月7日,下午4:18:38
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveCallRecord(Map<String, Object> map) {
		StringBuilder callRecordSql = new StringBuilder("insert into medical_call_record(id, extension, call_num, type, fee, call_time, end_time, seconds, relay, eid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		String type = "";
		String dst = map.get("dst").toString();
		
		// 判断电话类型（长途/市话）
		if(dst.matches("^0?1[34578]\\d{9}$")) {
			if(dst.charAt(0) == '0') {
				type = SystemConfig.CALL_LONG_TYPE;
			} else {
				type = SystemConfig.CALL_CITY_TYPE;
			}
		} else {									// 固话
			String areaCode = PhoneNumUtil.checkNumber(dst).toString().substring(PhoneNumUtil.checkNumber(dst).toString().lastIndexOf(",") + 1);
			if(StringUtils.isBlank(areaCode) || SystemConfig.LOCAL_CODE.equals(areaCode)) {
				type = SystemConfig.CALL_CITY_TYPE;
			} else {
				type = SystemConfig.CALL_LONG_TYPE;
			}
		}
		// 计算话费
		BigDecimal fee = new BigDecimal(0);
		// 基本费
		BigDecimal basicFee = new BigDecimal(0);
		// 资费设置记录
		Map<String, Object> basicCharges = this.findBasicCharges(map.get("trunk").toString(), type);
		
		if(!basicCharges.isEmpty()) {
			// 判断是否有优惠方案
			if(null != basicCharges.get("preferential")) {
				Map<String, Object> preferential = this.findPreferential(basicCharges.get("preferential").toString());
				Date startDate = (Date) preferential.get("start_date");
				Date endDate = (Date) preferential.get("end_date");
				if(new Date().after(startDate) && new Date().before(endDate)) {
					basicFee = new BigDecimal(preferential.get("fee").toString());
				}
			} else {
				basicFee = new BigDecimal(basicCharges.get("basic_fee").toString());
			}
			// 步长
			int startTime = Integer.parseInt(basicCharges.get("start_time").toString());
			// 计算话费
			fee = basicFee.divide(new BigDecimal(startTime), 2).multiply(new BigDecimal(map.get("billsec").toString()));
			// 操作人
			String operator = this.findAnswerSeat(map.get("src").toString());
			// 企业id
			String eid = this.findOutgoingEid(operator);
			jdbcTarget.update(callRecordSql.toString(), new Object[]{UuidUtil.getUuid(), map.get("src"), map.get("dst"), type, fee, map.get("calldate"), map.get("hangup_time"), map.get("billsec"), map.get("trunk"), eid});
		}
	}
	
	/**
	 * @desc 查询基本资费
	 * @param relay 中继id
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月7日,下午8:38:12
	 */
	public Map<String, Object> findBasicCharges(String relay, String type) {
		StringBuilder getChargesSql = new StringBuilder("select * from medical_tariff a where a.relay=? and a.type=? ");
		List<Map<String, Object>> list = jdbcTarget.queryForList(getChargesSql.toString(), new Object[]{relay, type});
		Map<String, Object> map = new HashMap<String, Object>();
		if(list.size() > 0) {
			map = list.get(0);
		}
		return map;
	}
	
	/**
	 * @desc 查询优惠方案
	 * @param preferential
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月7日,下午9:16:33
	 */
	public Map<String, Object> findPreferential(String preferential) {
		StringBuilder getChargesSql = new StringBuilder("select * from medical_preferential_plan a where a.id=? ");
		return jdbcTarget.queryForMap(getChargesSql.toString(), new Object[]{preferential});
	}
	
	/**
	 * @desc 保存录音表
	 * @param map
	 * @author kangyj
	 * @datetime 2015年1月7日,下午7:42:13
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveRecording(Map<String, Object> map) {
		StringBuilder recordingSql = new StringBuilder("insert into medical_call_recording(id, caller_num, called_num, seat, call_time, talk_time, call_province, call_city, center_id, group_id, user_id, is_open, recording_file, hospital_id) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
		// 操作人
		String seat = "";
		if(SystemConfig.OUT_TO_IN_TYPE.equals(map.get("call_type"))) {
			seat = this.findAnswerSeat(map.get("dst").toString());
		} else if (SystemConfig.IN_TO_OUT_TYPE.equals(map.get("call_type"))){
			seat = this.findAnswerSeat(map.get("src").toString());
		}
		// 查询组
		String groupId = "";
		String centerId = "";
		StringBuilder getGroupSql = new StringBuilder("select a.group_id, a.is_super from medical_admin a where a.id=? ");
		List<Map<String, Object>> list = jdbcTarget.queryForList(getGroupSql.toString(), new Object[]{seat});
		if(list.size() > 0 && !SystemConfig.SUPER_ADMIN.equals(list.get(0).get("is_super").toString())) {
			groupId = list.get(0).get("group_id").toString();
			// 查询中心
			StringBuilder getCenterSql = new StringBuilder("select a.platform_id from medical_group a where a.id=? ");
			List<Map<String, Object>> list1 = jdbcTarget.queryForList(getCenterSql.toString(), new Object[]{groupId});
			centerId = list1.get(0).get("platform_id").toString();
		}
		
		// 录音文件绝对路径
		String recordingfile = map.get("recordingfile").toString();
		
		// 企业id
		String eid = this.findOutgoingEid(seat);
		String address = "";
		if(StringUtils.isNotBlank(map.get("dst").toString())) {
			address = this.findDstAddress(map.get("dst").toString());
		}
		String province = "";
		String city = "";
		if(StringUtils.isNotBlank(address) && !SystemConfig.INVALIDPHONE.equals(address)) {
			province = address.substring(0, address.lastIndexOf("|"));
			city = address.substring(address.lastIndexOf("|") + 1);
		} else if(StringUtils.isBlank(address)) {								// 判断状态call_type，呼出失败是什么状态，过滤不规则号码3188819922
			province = "本地";
			city = "本地";
		}
		jdbcTarget.update(recordingSql.toString(), new Object[]{UuidUtil.getUuid(), map.get("src"), map.get("dst"), seat, map.get("calldate"), map.get("billsec"), province, city, centerId, groupId, seat, SystemConfig.ISOPEN0, recordingfile, eid});
	}
	
	/**
	 * @desc 查询接听坐席
	 * @param extension 分机号
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月5日,下午3:06:33
	 */
	public String findAnswerSeat(String extension) {
		StringBuilder getSeatSql = new StringBuilder("select id from medical_admin a where a.phone_number=? ");
		List<Map<String, Object>> list = jdbcTarget.queryForList(getSeatSql.toString(), new Object[]{extension});
		String seat = "";
		if(list.size() != 0) {
			seat = list.get(0).get("id").toString();
		}
		return seat;
	}
	
	/**
	 * @desc 查询座席所在的组
	 * @return
	 * @author kangyj
	 * @datetime 2015年3月8日,下午12:15:48
	 */
	public String findSeatGroup(String seatId) {
		StringBuilder getSeatGroup = new StringBuilder("select group_id from medical_admin a where a.id=? ");
		List<Map<String, Object>> list = jdbcTarget.queryForList(getSeatGroup.toString(), new Object[]{seatId});
		StringBuilder groupId = new StringBuilder("");
		if(list.size() != 0) {
			groupId.append(list.get(0).get("group_id").toString());
		}
		return groupId.toString();
	}
	
	/**
	 * @desc 查询坐席所在中心
	 * @param seatId
	 * @return
	 * @author kangyj
	 * @datetime 2015年3月8日,下午2:34:15
	 */
	public String findSeatCenter(String groupId) {
		StringBuilder getSeatCenter = new StringBuilder("select platform_id from medical_group a where a.id=? ");
		List<Map<String, Object>> list = jdbcTarget.queryForList(getSeatCenter.toString(), new Object[]{groupId});
		StringBuilder centerId = new StringBuilder("");
		if(list.size() != 0) {
			centerId.append(list.get(0).get("platform_id").toString());
		}
		return centerId.toString();
	}
	
	/**
	 * @desc 查询呼入电话企业id
	 * @param did 外线号码
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月5日,下午3:22:48
	 */
	public String findIncomingEid(String did) {
		StringBuilder getEidSql = new StringBuilder("select eid from medical_enterprise_outside a where a.outside_num=? ");
		List<Map<String, Object>> list = jdbcTarget.queryForList(getEidSql.toString().toString(), new Object[]{did});
		String eid = "";
		if(list.size() > 0) {
			eid = list.get(0).get("eid").toString();
		}
		return eid;
	}
	
	/**
	 * @desc 查询呼出电话企业id
	 * @param src
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月7日,下午2:06:23
	 */
	public String findOutgoingEid(String seat) {
		StringBuilder getEidSql = new StringBuilder("select enterprise_id from medical_admin a where a.id=? ");
		String eid = "";
		if(StringUtils.isNotBlank(seat)) {
			eid = jdbcTarget.queryForObject(getEidSql.toString(), String.class, new Object[]{seat});
		} 
		return eid;
	}
	
	/**
	 * @desc 查询呼入电话归属地
	 * @param src 号码
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月5日,下午3:41:42
	 */
	public String findSrcAddress(String src) {
		StringBuilder getMobileSql = new StringBuilder("select msheng, mshi from medical_mobile a where a.mc=? ");
		StringBuilder getTelSql = new StringBuilder("select msheng, mshi from medical_mobile a where a.mareacode=? ");
		List<Map<String, Object>> addList = new ArrayList<Map<String,Object>>();
		String address = "";
		String validResult = PhoneNumUtil.checkNumber(src).toString();
		String phoneType = validResult.substring(0, validResult.lastIndexOf(","));
		String phoneNum = validResult.substring(validResult.lastIndexOf(",") + 1);
		
		if(SystemConfig.CELLPHONE_TYPE.equals(phoneType)) {
			addList = jdbcTarget.queryForList(getMobileSql.toString(), new Object[]{phoneNum});		// 手机号归属地
		} else if(SystemConfig.FIXEDPHONE_TYPE.equals(phoneType) && StringUtils.isNotBlank(phoneNum)) {
			addList = jdbcTarget.queryForList(getTelSql.toString(), new Object[]{phoneNum});		// 固话归属地
		} else if(SystemConfig.FIXEDPHONE_TYPE.equals(phoneType)) {
			address = "";
		} else {
			address = "INVALIDPHONE";
		}
		if(addList.size() > 0) {
			address = addList.get(0).get("msheng").toString() + "|" + addList.get(0).get("mshi").toString();
		}
		return address;
	}
	
	/**
	 * @desc 查询呼出来电号码归属地
	 * @param dst
	 * @return
	 * @author kangyj
	 * @datetime 2015年1月6日,下午8:49:18
	 */
	public String findDstAddress(String dst) {
		StringBuilder getMobileSql = new StringBuilder("select msheng, mshi from medical_mobile a where a.mc=? ");
		StringBuilder getTelSql = new StringBuilder("select msheng, mshi from medical_mobile a where a.mareacode=? ");
		List<Map<String, Object>> addList = new ArrayList<Map<String,Object>>();
		
		String address = "";
		String validResult = PhoneNumUtil.checkNumber(dst).toString();
		String phoneType = validResult.substring(0, validResult.lastIndexOf(","));
		String phoneNum = validResult.substring(validResult.lastIndexOf(",") + 1);
		
		if(SystemConfig.CELLPHONE_TYPE.equals(phoneType)) {
			addList = jdbcTarget.queryForList(getMobileSql.toString(), new Object[]{phoneNum});		// 手机号归属地
		} else if(SystemConfig.FIXEDPHONE_TYPE.equals(phoneType) && StringUtils.isNotBlank(phoneNum)) {
			addList = jdbcTarget.queryForList(getTelSql.toString(), new Object[]{phoneNum});		// 固话归属地
		} else if(SystemConfig.FIXEDPHONE_TYPE.equals(phoneType)) {
			address = "";
		} else {
			address = "INVALIDPHONE";
		}
		if(addList.size() > 0) {
			address = addList.get(0).get("msheng").toString() + "|" + addList.get(0).get("mshi").toString();
		}
		return address;
	}
	
	/**
	 * @desc 批量更新已处理CDR数据
	 * @param cdrIds
	 * @author kangyj
	 * @datetime 2015年1月7日,上午11:32:55
	 */
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateCdrStatus(final List<Map<String, Object>> cdrList) {
		StringBuilder updateCdrSql = new StringBuilder("update cdr a set a.is_deal=? where a.id=? ");
		jdbcSrc.batchUpdate(updateCdrSql.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, SystemConfig.IS_DEAL_TYPE);
				arg0.setString(2, cdrList.get(arg1).get("id").toString());
			}
			@Override
			public int getBatchSize() {
				return cdrList.size();
			}
		});
	}
	
}
